# -*- coding: utf-8 -*-
import json,time,sqlite3,MySQLdb,threading,telnetlib
from Queue import Queue
from pysnmp.entity.rfc3413.oneliner import cmdgen
from pysnmp.proto import rfc1902

# 把列表转成sql的where in语句中的()部分
def c2sql(x):
  if len(x)==1:
    return str(tuple(x))[:-2]+")"
  else:
    return str(tuple(x))
    
# 从mysql的rnd表取ndip数据
def read_sqlite3():
  ips=[]
  conn=sqlite3.connect(r'lkt.db3')
  conn.text_factory = str
  [ips.append(x[0]) for x in conn.execute("select ndip from rnd where bAlive=1").fetchall()]
  conn.close()
  # print ips
  return ips

  
def do_write_sqlite3(l,bad_nd_r):

  s=time.time()
  last_change_time=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
  
  
  
  conn=sqlite3.connect(r'lkt.db3')
  conn.text_factory = str
  for x in l:
    if x[1] or x[2] or x[3]:
      if x[1] and x[2]:
        sql="update rnd set bSNMP=1,dtSNMP='"+last_change_time+"',sSNMP0='"+x[1]+"',sSNMP1='"+x[2]+"' where ndip='"+x[0]+"'"
      if x[1] and x[2]==None:
        sql="update rnd set bSNMP=1,dtSNMP='"+last_change_time+"',sSNMP0='"+x[1]+"',sSNMP1=Null where ndip='"+x[0]+"'"
      if x[1]==None and x[2]:
        sql="update rnd set bSNMP=1,dtSNMP='"+last_change_time+"',sSNMP0=Null,sSNMP1='"+x[2]+"' where ndip='"+x[0]+"'"
      if x[1]==None and x[2]==None:
        sql="update rnd set bSNMP=1,dtSNMP='"+last_change_time+"',sSNMP0=Null,sSNMP1=Null where ndip='"+x[0]+"'"
    else:
      sql="update rnd set bSNMP=0,dtSNMP='"+last_change_time+"',sSNMP0=Null,sSNMP1=Null where ndip='"+x[0]+"'"
      
    conn.execute(sql)
  for x in bad_nd_r:
    # print x
    conn.execute("insert into bnd(ndip,bReboot,sStatus,sSnmpdInfo,sSystemConfig,sHwInfo,sDump,sSoftwareVer,sBootromVer,sRunningTime,sBoardModel,sBoardVer,sEsn,sMac,sFlashSize,sSdramSize) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" , (x[0],1,x[1][0],x[1][1],x[1][2],x[1][3],x[1][4],x[1][5],x[1][6],x[1][7],x[1][8],x[1][9],x[1][10],x[1][11],x[1][12],x[1][13]))

  conn.commit()
  conn.close()
  print "write_sqlite3:",time.time()-s

  
  
  
  
def write_sqlite3(l):
  bad_nd=[]
  bad_nd_r=[]
  for x in l:
    if not(x[1] or x[2] or x[3]):
      bad_nd.append(x[0])
      print x

  for x in bad_nd:
    r=telnetdo(x)
    if r:
      bad_nd_r.append((x,r))
  
  write_success=False
  while not write_success:
    try:
      do_write_sqlite3(l,bad_nd_r)
      write_success=True
    except sqlite3.OperationalError,e:
      time.sleep(1)
    
  
  
def login():
  print "---start login---"
  tn.read_until("name:",2)
  for x in "root":
    tn.write(x)
    tn.read_until(x,2)
  tn.write("\r\n")
  s=tn.read_until("assword:",2)
  if s.find("name is error")>=0:
    return False
  for x in "admin":
    tn.write(x)
    tn.read_until("*",2)
  tn.write("\r\n")
  s=tn.read_until("->",2)
  if s.find("word is error")>=0:
    return False
  print "---end login---"
  return True
def debug():
  print "---start debug---"
  tn.write("debug\r\n")
  print tn.read_until("assword:",2)
  for x in "ytnbx2008.":
    tn.write(x)
    tn.read_until("*",2)
  tn.write("\r\n")
  print tn.read_until("DEBUG:->",2)
  print "---end debug---"
  return True
def checksnmpdown():
  print "---start check snmp---"
  tn.write(':show-task:tname="tSnmpd";\r\n')
  c=tn.read_until("DEBUG:->",2)
  # print c
  print "---end check snmp---"
  if c.find("STOP")<>-1:
    print "STOP"
    return "STOP",c
  if c.find("READY")<>-1:
    print "READY"
    return "READY",c
  return False
  
def showsystemconfig():
  tn.write(':show-system-config;\r\n')
  c=tn.read_until("DEBUG:->",5)
  return c
def showdump():
  tn.write(':show-dump;\r\n')
  c=tn.read_until("DEBUG:->",5)
  return c
def showhwinfo():
  tn.write(':show-hw-info;\r\n')
  c=tn.read_until("DEBUG:->",5)
  return c

def showsoftver(ssc):
  s=ssc.index("Software Version:")+len("Software Version:")
  e=ssc.index("Bootrom Version:")
  return ssc[s:e].strip()
def showbootver(ssc):
  s=ssc.index("Bootrom Version:")+len("Bootrom Version:")
  e=ssc.index("Compile TimeStamp:")
  return ssc[s:e].strip()
def showruntime(ssc):
  s=ssc.index("Running Time:")+len("Running Time:")
  e=ssc.index("Bridge Aging Time:")
  r=ssc[s:e].strip()
  r=r.replace(" Days ","D")
  r=r.replace(" Hours ",":")
  r=r.replace(" Minutes ",":")
  r=r.replace(" Seconds","")
  return r
def showboardmoddel(shi):
  s=shi.index("Board Model:")+len("Board Model:")
  e=shi.index("Board Version:")
  return shi[s:e].strip()
def showboardver(shi):
  s=shi.index("Board Version:")+len("Board Version:")
  e=shi.index("ESN:")
  return shi[s:e].strip()
def showesn(shi):
  s=shi.index("ESN:")+len("ESN:")
  e=shi.index("Ethernet 0 MAC:")
  return shi[s:e].strip()
def showmac(shi):
  s=shi.index("Ethernet 0 MAC:")+len("Ethernet 0 MAC:")
  e=shi.index("Flash Size:")
  return shi[s:e].strip()
def showflash(shi):
  s=shi.index("Flash Size:")+len("Flash Size:")
  e=shi.index("SDRAM Size:")
  return shi[s:e].strip().replace("(Mbytes)","M")
def showsdram(shi):
  s=shi.index("SDRAM Size:")+len("SDRAM Size:")
  e=shi.index("DEBUG:->")
  return shi[s:e].strip().replace("(Mbytes)","M")
  
def reboot():
  tn.write("reboot\r\n")
  print tn.read_until('Are you sure to reboot system?("Y" or Other)',5)
  tn.write("Y\r\n")
  return True
  
def telnetdo(HOST):
  try:
    print "Connecting to "+HOST
    tn.open(HOST)
    if not login():
      tn.close()
      return False
    if not debug():
      tn.close()
      return False
    snmp_status=checksnmpdown()
    if snmp_status:
      snmp_status=list(snmp_status)
      ssc=showsystemconfig()
      shi=showhwinfo()
      sd=showdump()
      sswv=showsoftver(ssc)
      sbrv=showbootver(ssc)
      srt=showruntime(ssc)
      sbm=showboardmoddel(shi)
      sbv=showboardver(shi)
      sesn=showesn(shi)
      smac=showmac(shi)
      sfs=showflash(shi)
      sss=showsdram(shi)
      snmp_status.append(ssc)
      snmp_status.append(shi)
      snmp_status.append(sd)
      snmp_status.append(sswv)
      snmp_status.append(sbrv)
      snmp_status.append(srt)
      snmp_status.append(sbm)
      snmp_status.append(sbv)
      snmp_status.append(sesn)
      snmp_status.append(smac)
      snmp_status.append(sfs)
      snmp_status.append(sss)
      
      if reboot():
        # tn.close()
        return snmp_status
    else:
      tn.close()
      return False
  except:
    print "Connect Error!"
    return False
def getsnmp(ndip,oid):
  errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().getCmd(cmdgen.CommunityData(ndip, 'public'),cmdgen.UdpTransportTarget((ndip, 161)),oid)
  if errorIndication:
    # print errorIndication
    snmp=None
  else:
    if errorStatus:
      # print '%s at %s\n' % (
        # errorStatus.prettyPrint(),
        # errorIndex and varBinds[int(errorIndex)-1] or '?'
        # )
      snmp=None
    else:
      for name, val in varBinds:
        snmp=val.prettyPrint()
  return snmp

def setsnmp(ndip,oid,type,value):
  if type=="OctetString":
    errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().setCmd(cmdgen.CommunityData(ndip, 'private'),cmdgen.UdpTransportTarget((ndip, 161)),(oid, rfc1902.OctetString(value)))
  if type=="IpAddress":
    errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().setCmd(cmdgen.CommunityData(ndip, 'private'),cmdgen.UdpTransportTarget((ndip, 161)),(oid, rfc1902.IpAddress(value)))
  if type=="Integer":
    errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().setCmd(cmdgen.CommunityData(ndip, 'private'),cmdgen.UdpTransportTarget((ndip, 161)),(oid, rfc1902.Integer(value)))
    
  if errorIndication:
    print errorIndication
  else:
    if errorStatus:
      print '%s at %s\n' % (
        errorStatus.prettyPrint(),
        errorIndex and varBinds[int(errorIndex)-1] or '?'
        )
    else:
      for name, val in varBinds:
        print '%s = %s' % (name.prettyPrint(), val.prettyPrint())
  
#具体的处理函数，负责处理单个任务
def do_somthing_using(arguments):
  lNode=[]
  s0=s1=None
  ip=getsnmp(arguments,(1,3,6,1,4,1,5959,1,1,1,1,1,2,1))
  s0=getsnmp(arguments,(1,3,6,1,4,1,5959,1,1,11,1,1,3,1,0))
  s1=getsnmp(arguments,(1,3,6,1,4,1,5959,1,1,11,1,1,3,1,1))
  if ip==arguments and (s0<>'172.16.100.31' or s1<>'172.16.100.40'):
    if s0<>'172.16.100.31':
      setsnmp(arguments,(1,3,6,1,4,1,5959,1,1,11,1,1,3,1,0),'IpAddress','172.16.100.31')
    if s1<>'172.16.100.40':
      setsnmp(arguments,(1,3,6,1,4,1,5959,1,1,11,1,1,3,1,1),'IpAddress','172.16.100.40')
    setsnmp(arguments,(1,3,6,1,4,1,5959,1,1,4,1,1,11,1),"Integer","1")
  lNode.append(arguments)
  lNode.append(s0)
  lNode.append(s1)
  lNode.append(ip)
  # print lNode
  lSNMP.append(lNode)
  # print lNode
  
#这个是工作进程，负责不断从队列取数据并处理
def working():
  while True:
    arguments = q.get()
    do_somthing_using(arguments)
    q.task_done()
    
def SNMP_Poll(nds):
  s=time.time()
  #把JOBS排入队列
  for i in nds:
    q.put(i)
  #等待所有JOBS完成
  q.join()
  print time.time()-s
  
q = Queue()
#fork NUM个线程等待队列
NUM = 1
for i in range(NUM):
  t = threading.Thread(target=working)
  t.setDaemon(True)
  t.start()

while True:
  delaytime=time.time()
  lSNMP=[]
  nds=read_sqlite3()
  config = json.load(open("config.txt",'r'))
  print "starting poll..."
  SNMP_Poll(nds)
  print "threading:",threading.activeCount()
  tn = telnetlib.Telnet()
  write_sqlite3(lSNMP)
  del tn
  delaytime=config["SNMP_Poll_Time"]-(time.time()-delaytime)
  if delaytime<=0:
    delaytime=config["SNMP_Poll_Time"]
  print "sleep time",delaytime
  time.sleep(delaytime)
